# @Time    : 2019/12/31 15:50
# @File    : Excel多个Sheet合并.py
# @Date    : 2019/12/31
# @Author  : Yuwenjun
# @Desc    : 合并Excel多个sheet
import time

import pandas as pd
import os
import pymysql
from pymongo import MongoClient
import requests

# db = pymysql.connect(host='192.168.0.37', user='will_art', passwd='123456', db='will_art')
# client = MongoClient("192.168.0.37", 27017)
# mongodb = client.tasks
# mongodb.authenticate('tasks', '123456')

# db = pymysql.connect(host='39.97.250.105', user='will_art', passwd='123456', db='will_art')
client = MongoClient("39.97.250.105", 27018)
mongodb = client.tasks
mongodb.authenticate('tasks', '123456')


class MergeExcel:
    def __init__(self, header, col, sample, is_filter_number, input_path):
        self.input_path = input_path
        self.header = header
        self.col = col
        self.sample = sample
        self.is_filter_number = is_filter_number
        self.col_tag = False

    def read_excel(self, path):
        df = None
        xls_data = pd.ExcelFile(path)
        for sheet_name in xls_data.sheet_names:
            _df = xls_data.parse(sheet_name=sheet_name, header=self.header, encoding='utf-8')
            # _df = _df.loc[~_df[0].str.contains("[0-9]")] if self.is_filter_number else _df

            df = pd.concat([df, _df], ignore_index=True)
            if sheet_name == "泰米尔不合格":
                print("泰米尔不合格")
                # 第一列的长度
                lenth = len(_df[0])
                n = 1
                while n < lenth:
                    # 音频名称
                    audio_name = _df[0][n]
                    # audioName_new = self.deal_string(audio_name)

                    reversal_string = list(audio_name)
                    audio_name = "".join(reversal_string[::-1])
                    # audio_name = self.reversal(audio_name)
                    audio_names = audio_name.split("_", 1)
                    audio_names_one = audio_names[0].split(".", 1)
                    audio_name_new = audio_names_one[0] + "." + audio_names[1]
                    audio_name_new_ = self.reversal(audio_name_new)

                    # 文本名称
                    text_name = _df[1][n]

                    # 任务名称 第三列
                    task_name = _df[2][n]
                    taskInfo = get_task_info_by_name(task_name)
                    print(taskInfo)
                    dateset_name = taskInfo[0]['dataset']['colName']

                    text_info = get_text_info_by_colNameAndaudio_anme(dateset_name, audio_name_new_)
                    print(text_info)

                    r = requests.get(text_info[0]['url'])
                    path = "d:\\tamil\\"
                    #文件夹不存在就创建
                    if not os.path.exists(path):
                        os.makedirs(path)
                    with open(path + audio_name_new_, "wb") as code:
                        code.write(r.content)

                    n += 1

                for xls_data_one in _df:
                    print(xls_data_one)

        if self.col is None:
            return df.drop_duplicates()
        elif not self.col_tag:
            return df[[int(index) for index in self.col.split(",")]].dropna().drop_duplicates()
        else:
            return df[[index for index in self.col.split(",")]].dropna().drop_duplicates()

    # 反转字符串
    def reversal(self, text_name):
        reversal_string = list(text_name)
        return "".join(reversal_string[::-1])
        # 处理字符

    def deal_string(self, text_name):
        # 反转字符
        text_name = self.reversal(text_name)
        text_names = text_name.split("_", 1)
        text_names_one = text_names[0].split(".", 1)
        text_name_new = text_names_one[0] + "." + text_names[1]
        return self.reversal(text_name_new)

    def run(self):
        if os.path.isdir(self.input_path):
            excel_names = [name for name in os.listdir(self.input_path) if name.endswith((".xls", ".xlsx"))]
        
        _df = self.read_excel(self.input_path)

def get_task_info_by_name(task_name):
    # 数据集
    tasks = mongodb['tasks']
    # taskinfos = list(tasks.find({'taskName': {"$in": [name for name in task_name.split(",")]}}))
    taskinfos = list(tasks.find({'taskName': task_name}))
    # taskinfos = tasks.find({'taskName':  task_name })
    # taskinfos = tasks.find({'taskName': {"$in": [name for name in task_name.split(",")]}})
    return taskinfos


def get_text_info_by_colNameAndaudio_anme(colName, audio_name):
    # 数据集
    dataset = mongodb[colName]
    # taskinfos = list(tasks.find({'taskName': {"$in": [name for name in task_name.split(",")]}}))
    # col_info = list(dataset.find({'url':  re.compile(audio_name)}))
    col_info = list(dataset.find({'url': {"$regex": "300.wav"}}))
    # taskinfos = tasks.find({'taskName':  task_name })
    # taskinfos = tasks.find({'taskName': {"$in": [name for name in task_name.split(",")]}})
    return col_info


if __name__ == '__main__':
    header = ""
    col = ""
    is_filter_number = ""
    sample = ""
    # header = input("请确认Excel是否包含表头，0无表头，其它有表头，默认为0：")
    # col = input("请指定Excel各sheet需要合并的列，0代表A列，依此类推，默认合并全部：")
    # is_filter_number = input("请确认是否过滤数字，输入1过滤，默认不过滤：")
    # sample = input("是否需要抽样合并，若需要输入每份Excel随机抽取的行数（大于1的整数）或者百分比（小数），\n默认不抽样：")

    # input_path = input("请输入Excel文件夹路径或者单个文件地址：")
    input_path = "C:\\Users\\Admin\\Desktop\\test\\不合格数据整理.xlsx"
    if not input_path:
        print("请确认Excel的文件夹存放路径是否输入完整")
        exit(1)
    config = {
        "header": None if not header else header,
        "col": None if not col else col,
        "sample": None if not sample else sample,
        "is_filter_number": True if is_filter_number == "1" else False,
        "input_path": input_path
    }

    main = MergeExcel(**config)
    main.run()

    # C:\Users\Admin\Desktop\test\泰米尔不合格音频名称.xlsx
    # C:\Users\Admin\Desktop\test\不合格数据整理.xlsx
